﻿
package com.feib.ebilling.job.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.feib.ebilling.job.service.TelnetService;


/**
 * @title (#)ThirdPartyFeedbackDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/12/10 create by Jimmy Liu<br>
 */


@Repository("thirdPartyFeedbackDao")
public class ThirdPartyFeedbackDao extends BaseDao {
    
    private SimpleJdbcInsert feedbackLogInsertor;
    
    private void chkDBConnection(int iTimes){
    	boolean bflag = false;    	
    	/* 20130107 網路問題目前無解
    	 * 有時會發生連接到主機 10.48.101.84 的 TCP/IP 連接已經失敗。
    	 * 錯誤: "connect timed out 
    	 */
    	int i=0;
    	while (bflag == false){
    		/* 確認資料庫是OK的 */
    		TelnetService telnetService = new TelnetService();
    		bflag = telnetService.connect();
    		i++;
    		if (bflag == false) {
	    		if (i < iTimes){
	    		    logger.info("資料庫連線失敗第{}次.", i);
	    		}else{
	    			logger.error("資料庫無法連線重試{}次.", i);
	    		}
    		}else{
    			logger.info("資料庫連線成功.");
    		}
    	}
    }    
    
    public Number insertFeedbackLog(Map<String, Object> parameters)
    {
        if (null == feedbackLogInsertor)
        {
        	/* 檢核資料庫是否有斷線 */
        	chkDBConnection(5);        	
        	String[] columns = new String[]{
                            "FBFILENAME", "FBTIME", "FBCODE", "MRKEY", "ADAY"
                            , "ATIME", "TBSDY", "APTYPE", "TXNO", "DSCPT"
                            , "TXAMT", "USERNO", "ATMID", "KINBR", "TRMSEQ"
                            , "TXTNO", "TFRBK", "TFRACT", "STANBK", "STANSEQ"
                            , "EC", "FEEDBACK_TYPE", "CHANNEL", "STORE", "ACCTDATE"
                            , "BARCDOE1", "BARCDOE2", "BARCDOE3", "ERR_MSG"
                            };
            feedbackLogInsertor = new SimpleJdbcInsert(this.getDataSource()).withTableName("FEEDBACK_LOG")
            .usingColumns(columns)
            .usingGeneratedKeyColumns("UID");
        }
        return feedbackLogInsertor.executeAndReturnKey(parameters);
    }

    /**
     * @param fileName
     * @return
     */
    public int deleteFeedbackLog(String fileName) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from FEEDBACK_LOG where FBFILENAME = ?");
        return this.getJdbcTemplate().update(sql.toString(), fileName );
    }

    /**
     * @param virtualAccount
      * @return
     */
    public List<Map<String, Object>> queryUnWriteOffByVirtualAccount(String virtualAccount) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE")
        .append(" from PAYMENT where VIRTUAL_ACCOUNT ='").append(virtualAccount).append("' AND PAYMENT_STATUS ='2'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }

    /**
     * @param post_acct
     * @return
     */
    public List<Map<String, Object>> queryGroupForPostAcct(String postAcct) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT GROUP_PK, GROUP_NO, GROUP_NAME")
        .append(" FROM APP_GROUP WHERE POST_ACCT ='")
        .append(postAcct)
        .append("'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }
    
    /**
     * @param payerID
     * @param group_pk
     * @return
     */
    public List<Map<String, Object>> queryUnWriteOffByPayerIDForPost(String payerID, String group_pk) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE")
        .append(" from PAYMENT where VIRTUAL_ACCOUNT ='").append(payerID).append("' AND GROUP_PK in (").append(group_pk).append(") AND PAYMENT_STATUS ='2'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    }
    
    /**
     * @param barcode1
     * @param barcode2
     * @param barcode3
     * @return
     */
    public List<Map<String, Object>> queryUnWriteOffByBarcode(String barcode1, String barcode2, String barcode3) {

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT PAYMENT_PK, VIRTUAL_ACCOUNT, TOTAL_AMOUNT, TOTAL_LOANABLE_AMOUNT ")
        .append(", BARCODE_STORES_1, BARCODE_STORES_2, BARCODE_STORES_3 ")
        .append(", BARCODE_STORES_1_LOAN, BARCODE_STORES_2_LOAN, BARCODE_STORES_3_LOAN ")
        .append(", PAY_DUE")
        .append(" FROM PAYMENT ")
        .append(" WHERE PAYMENT_STATUS ='2'")
        .append(" AND ( (BARCODE_STORES_1 = ? AND BARCODE_STORES_2=? AND BARCODE_STORES_3 = ? )")
        .append("  OR (BARCODE_STORES_1_LOAN = ? AND BARCODE_STORES_2_LOAN = ? AND BARCODE_STORES_3_LOAN = ?) )");
        return this.getJdbcTemplate().queryForList(sql.toString(), barcode1, barcode2, barcode3, barcode1, barcode2, barcode3);
    }
    
    public List<String> loadCommissionCodes() {
        StringBuffer sql = new StringBuffer();
        sql.append("select distinct COMMISSION_CODE from COMMISSION_CODE_TABLE where COMMISSION_CODE != '000' and COMMISSION_CODE != 'ZZZ' and COMMISSION_CODE != 'XXX'");
        
        List<Map<String, Object>> rds = this.getJdbcTemplate().queryForList(sql.toString());
        List<String> result = new ArrayList<String>();
        for(Map<String, Object> rd : rds)
            result.add((String)rd.get("COMMISSION_CODE"));
        
        rds.clear();
        
        return result;
    }
    
    public List<Map<String, Object>> queryFeedbacklogByGroupType(String virtualAccount) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT A.UID, A. FBFILENAME, A.FBTIME, A.ADAY, A.ATIME, A.ERR_MSG, A.CHANNEL AS ")
        .append("FEEDBACK_CHANNEL, A.TXAMT, A.USERNO , B.* FROM FEEDBACK_LOG A  JOIN ")
        .append("(SELECT G.GROUP_PK ,G.GROUP_NO, G.GROUP_NAME, R.VIRTUAL_ACCOUNT_NO ")
        .append(",LEN(R.VIRTUAL_ACCOUNT_NO) AS length FROM REAL_ACCOUNT R  JOIN APP_GROUP G ON ")
        .append("R.GROUP_PK = G.GROUP_PK WHERE G.GROUP_TYPE ='4') B  ON ")
        .append("left(RTRIM(A.USERNO),B.length) = B.VIRTUAL_ACCOUNT_NO where A.USERNO ='")
        .append(virtualAccount).append("' ");
        return this.getJdbcTemplate().queryForList(sql.toString());
    } 
    
    
    public List<Map<String, Object>> loadAchEdiid() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT   GROUP_PK,GROUP_NAME,ACH_EDIID FROM APP_GROUP WHERE ACH_EDIID IS NOT NULL ");
        return  this.getJdbcTemplate().queryForList(sql.toString());
   
    }
    
    /**
     * 讀取資料庫 取得 銷帳資料的DB名稱為"AR_Data" + B304_ProfileID
     * @param ACH_EDIID
     * @return
     */
    public Map<String, Object > getAchDBName(String ACH_EDIID) {
    	StringBuffer sql = new StringBuffer();
        sql.append("Select B304_ProfileID,B302_CompanyName from AR.dbo.vCompanyProfileID where B302_TaxNo= ? "); 
         
        try{
            return  this.getJdbcTemplate().queryForMap(sql.toString(),ACH_EDIID);
        }catch (EmptyResultDataAccessException e) {
 			return null;
 		}
    } 
    
    
    /**
     * 讀取資料庫取得銷帳資料的Table名稱為 "D" + B309_UploadFileDefID
     * @param B304_ProfileID
     * @return
     */
    public Map<String, Object > getAchTableName(String B304_ProfileID) {
    	StringBuffer sql = new StringBuffer();
        sql.append("Select B309_UploadFileDefID,B311_UploadFileID ")
           .append(" from  AR.dbo.B311, AR.dbo.B309 ")
           .append(" where (B311_UploadFileID = B309_UploadFileID)")
           .append(" and  B311_UploadCode = 'AUCT' ")
           .append(" and B311_ProfileID= ?  ");
        try{	
        	return  this.getJdbcTemplate().queryForMap(sql.toString(),B304_ProfileID);
        }catch (EmptyResultDataAccessException e) {
  			return null;
  		}
    } 
    
    
    /**
     * 用第3(getAchDBName)、5項 (getAchTableName)的DB名稱 + Table名稱，組查詢SQL
     * @param DBName
     * @param TableName
     * @param processSysDateROC
     * @return
     */
    public List<Map<String, Object>> getFeedbacklogFromACH(String DBName,String TableName,String processSysDateROC) {
        StringBuffer sql = new StringBuffer();
        /* 20120521 增加取得 ACH銷帳資料 */
        /* Select * FROM AR_Data83065.dbo.D226 where D226_F101=系統8碼民國年日期(01000426) and D226_F10=’00’*/
        sql.append("Select * FROM ").append(DBName).append(".dbo.").append(TableName)
           .append(" where " + TableName + "_F101= ? ").append(" and " + TableName + "_F10='00' ");
        
        return this.getJdbcTemplate().queryForList(sql.toString(),processSysDateROC);
    } 
    
    /**
     * 取得帳單業者設定，虛擬帳號
     * @param groupPk
     * @return
     */
    public List<String> loadVirtualAccountNo(String groupPk) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  VIRTUAL_ACCOUNT_NO  FROM REAL_ACCOUNT where group_pk= ? ");
        
        List<Map<String, Object>> rds = this.getJdbcTemplate().queryForList(sql.toString(),groupPk);
        List<String> result = new ArrayList<String>();
        for(Map<String, Object> rd : rds)
            result.add((String)rd.get("VIRTUAL_ACCOUNT_NO"));
        
        rds.clear();
        
        return result;
    }
    
    
    /**
     * @param AchEdiid
     * @return
     */
    public List<Map<String, Object>> queryGroupForAchEdiid(String achEdiid) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT GROUP_PK, GROUP_NO, GROUP_NAME,ACH_EDIID")
        .append(" FROM APP_GROUP WHERE ACH_EDIID ='").append(achEdiid).append("'");
        return this.getJdbcTemplate().queryForList(sql.toString());
    } 
        
    public  String getHoliday(String processDate){

    	/* 20120521 ACH銷帳使用processDate 轉民國年    系統8碼民國年日期(01000426) */
    	
    	String processSysDateROC = String.format( "%1$08d", Integer.parseInt(processDate) - 19110000); 
    	
    	StringBuffer sql = new StringBuffer();
    	
            /* 使用processDate 轉民國年取得營業日 */
    		sql.append("Select * From AR.dbo.BP004 ")
               .append(" where BP004_TBSDY = '" + processSysDateROC + "'" );   
    		
            List<Map<String, Object>> rds = this.getJdbcTemplate().queryForList(sql.toString());
            /* 判斷是否為假日 */
            String holiday = "";
            /* 記錄前一個營業日 sLastBussinessDay 當成F101的查詢日期。*/
            String sLastBussinessDay = "";            
            for(Map<String, Object> rd : rds){
            	 holiday = ((String)rd.get("BP004_HOLIDY"));
                 sLastBussinessDay = ((String)rd.get("BP004_LBSDY"));               
            }          
            if (rds.size()==0){            	
               	return null;
            }else{  
            	/* 假日不回饋檔案 */
                if ("000001".equals(holiday)){                   
                    return holiday; 
                }
                /* 記錄前一個營業日 sLastBussinessDay 當成F101的查詢日期 。*/
                return sLastBussinessDay;
            }
    
    }       

    
}
